<?php
	include("../connect/connect_dsccmis.php");
	
	$sql = "drop table if exists mis_numvictim_trend;";
	$rs=mysql_query($sql);
	
	$sql="create table mis_numvictim_trend as select t1.prov,t1.ym,t1.cdscc,t2.c from
(select prov,concat(yyyy,mon) as ym,cdscc from mis_const_prov,mis_const_year,mis_const_month,mis_const_cdscc)
t1 left join 
(select prov,ym,cdscc,count(*) as c from 
(select perid,left(t1.evndte,6) as ym,left(t1.evnplcidgen,2) as prov,left(t1.evnplcidgen,4) as dis,cdscc,mis_losbody
 from dscc.viewlosofper t1 inner join dscc.TempEventAndCaseOfEvent t2 on t1.evnid=t2.evnid
inner join convert_losbody t3 on t1.losbody=t3.losbody) t1 
 where (mis_losbody = '1' or mis_losbody ='2') and 
(prov in ('94','95','96') or dis in ('9003','9004','9005','9006'))
group by prov,ym,cdscc) t2 on t1.prov=t2.prov and t1.ym=t2.ym and t1.cdscc=t2.cdscc";
	$rs=mysql_query($sql);
    //เปลี่ยนค่า null ให้เป็น 0
	$sql = "update mis_numvictim_trend set c=0 where c is null";
	$rs=mysql_query($sql);
	
	//mysql_free_result($rs);
	//mysql_close($c);
	echo "<br>complete create mis_numvictim_trend on dsccmis";
?>